/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package ysw.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ysw.dao.FixDao;
import ysw.databaseconnection.DBConn;
import ysw.vo.Fix;

/**
 *
 * @author Administrator
 */
public class FixJdbc implements FixDao{
 private  Connection conn=null;
    private PreparedStatement psmt=null;
    private ResultSet rs=null;
    public FixJdbc(){}
    public Connection getConn(){
        try{
            if(this.conn==null||this.conn.isClosed()){
                DBConn mc=new DBConn();
                this.conn=mc.getConn();
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return conn;
    }
   

 public Fix addFix(Fix fix){
        String sql="insert into fixs(ftime,ftype,carnumber,cid,cname,worktime,price,state) values(?,?,?,?,?,?,?,?)";
        try{
            psmt=this.getConn().prepareStatement(sql);
            //psmt.setInt(1,fix.getFid());
            psmt.setDate(1,fix.getFtime());
            psmt.setByte(2,fix.getFtype());
            psmt.setString(3,fix.getCarnumber());
            psmt.setInt(4,fix.getCid());
            psmt.setString(5,fix.getCname());
            psmt.setInt(6, fix.getWorktime());
            psmt.setDouble(7,fix.getPrice());
            psmt.setByte(8,fix.getState());
       
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return fix;
        } 


public List showFix() throws SQLException{
    String sql="select * from fixs";
    List fixList=new ArrayList();
    try{
        psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
          Fix fix=new Fix();
          fix.setFid(rs.getInt("fid"));
          fix.setFtime(rs.getDate("ftime"));
          fix.setFtype(rs.getByte("ftype"));
          fix.setCarnumber(rs.getString("carnumber"));
          fix.setCid(rs.getInt("cid"));
          fix.setCname(rs.getString("cname"));
          fix.setWorktime(rs.getInt("worktime"));
          fix.setPrice(rs.getDouble("price"));
          fix.setState(rs.getByte("state"));
            
           fixList.add(fix);
   
        }
        return fixList;
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
             if(rs!=null){
                   rs.close();
                   rs=null;
                }
                if(psmt!=null){
                    psmt.close();
                    psmt=null;
                }
                if(conn!=null){
                    conn.close();
                    conn=null;
                }
            }catch(SQLException e){
                        e.printStackTrace();
                        }
            }
            return fixList;
} 
public Fix showOneFix(Integer fid){
 ResultSet rs=null;
    String sql="select * from fixs where fid="+fid;
 Fix fix=new Fix();
    try{
      psmt=this.getConn().prepareStatement(sql);
        rs=psmt.executeQuery();
        while(rs.next()){
        
          fix.setFid(rs.getInt("fid"));
          fix.setFtime(rs.getDate("ftime"));
          fix.setFtype(rs.getByte("ftype"));
          fix.setCarnumber(rs.getString("carnumber"));
          fix.setCid(rs.getInt("cid"));
          fix.setCname(rs.getString("cname"));
          fix.setWorktime(rs.getInt("worktime"));
          fix.setPrice(rs.getDouble("price"));
          fix.setState(rs.getByte("state"));
        }
 
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
              
            }catch(SQLException e){
                        e.printStackTrace();
                        }
        try{conn.close();
        
        }catch(SQLException e){
             e.printStackTrace();
        }
            }
            return fix;
 }





 public Fix updateSaveFix(Fix fix){
    String sql="update fixs set fid=?,ftime=?,ftype=?,carnumber=?,cid=?,cname=?,worktime=?,price=?,state=? where fid="+fix.getFid();
 
     try{
            
          psmt=this.getConn().prepareStatement(sql);
          
        psmt.setInt(1, fix.getFid());
            psmt.setDate(2,fix.getFtime());
            psmt.setByte(3,fix.getFtype());
            psmt.setString(4,fix.getCarnumber());
            psmt.setInt(5,fix.getCid());
            psmt.setString(6,fix.getCname());
            psmt.setInt(7, fix.getWorktime());
            psmt.setDouble(8,fix.getPrice());
            psmt.setByte(9,fix.getState());
      
          
            psmt.execute();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
         try{
             psmt.close();
            
         }catch(SQLException e){
             e.printStackTrace();
         }
     }
     return fix;
} 
 public void deleteFix(Integer fid){
    String sql="delete from fixs where fid="+fid;
    System.out.println(fid+"fixJdbc");
    try{
        psmt=this.getConn().prepareStatement(sql);
        psmt.execute();
    }catch(Exception e){
        e.printStackTrace();
    }finally{
        try{
            psmt.close();
        }catch(SQLException e){
                e.printStackTrace();
        }
        try{
            conn.close();
        }catch(SQLException e){
                e.printStackTrace();
        }
    }
}  
}
